﻿namespace AnyOffice.DAL
{
    using AnyOffice.Model;
    using Microsoft.ApplicationBlocks.Data;
    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Text;

    public class GroupDao
    {
        public bool Delete(SqlConnection conn, string userId, int groupId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("DELETE FROM [dbo].[Group] ");
            builder.Append("WHERE ");
            builder.Append("\t[GroupId] = @GroupId ");
            builder.Append("\tAND [UserId] = @UserId ");
            SqlParameter[] commandParameters = new SqlParameter[2];
            commandParameters[0] = new SqlParameter("@GroupId", SqlDbType.Int);
            commandParameters[0].Value = groupId;
            commandParameters[1] = new SqlParameter("@UserId", SqlDbType.NVarChar, 20);
            commandParameters[1].Value = userId;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, builder.ToString(), commandParameters);
            return true;
        }

        public int Insert(SqlConnection conn, GroupData data)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("INSERT INTO [dbo].[Group] ");
            builder.Append("( ");
            builder.Append("\t[UserId], ");
            builder.Append("\t[GroupName], ");
            builder.Append("\t[GroupNo], ");
            builder.Append("\t[UserStr] ");
            builder.Append(") ");
            builder.Append("VALUES ");
            builder.Append("( ");
            builder.Append("\t@UserId, ");
            builder.Append("\t@GroupName, ");
            builder.Append("\t@GroupNo, ");
            builder.Append("\t@UserStr ");
            builder.Append(") ");
            builder.Append("SELECT SCOPE_IDENTITY() AS RetValue ");
            SqlParameter[] commandParameters = new SqlParameter[4];
            commandParameters[0] = new SqlParameter("@UserId", SqlDbType.NVarChar, 20);
            commandParameters[0].Value = data.UserId;
            commandParameters[1] = new SqlParameter("@GroupName", SqlDbType.NVarChar, 50);
            commandParameters[1].Value = data.Name;
            commandParameters[2] = new SqlParameter("@GroupNo", SqlDbType.NVarChar, 20);
            commandParameters[2].Value = data.GroupNo;
            commandParameters[3] = new SqlParameter("@UserStr", SqlDbType.NText);
            if (data.UserStrIsNull)
            {
                commandParameters[3].Value = DBNull.Value;
            }
            else
            {
                commandParameters[3].Value = data.UserStr;
            }
            object obj2 = SqlHelper.ExecuteScalar(conn, CommandType.Text, builder.ToString(), commandParameters);
            if (obj2 != null)
            {
                return Convert.ToInt32(obj2);
            }
            return -1;
        }

        public DataTable SelectAll(SqlConnection conn, string userId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("SELECT ");
            builder.Append("\t[GroupId], ");
            builder.Append("\t[UserId], ");
            builder.Append("\t[GroupName], ");
            builder.Append("\t[GroupNo], ");
            builder.Append("\t[UserStr] ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[Group] ");
            builder.Append("WHERE ");
            builder.Append("\t[UserId] = @UserId ");
            builder.Append("ORDER BY ");
            builder.Append("\t[GroupNo] ASC ");
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@UserId", SqlDbType.NVarChar, 20) };
            commandParameters[0].Value = userId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString(), commandParameters);
            set.Tables[0].TableName = "Group";
            return set.Tables[0];
        }

        public DataTable SelectByGroupId(SqlConnection conn, string userId, int groupId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("SELECT ");
            builder.Append("\t[GroupId], ");
            builder.Append("\t[UserId], ");
            builder.Append("\t[GroupName], ");
            builder.Append("\t[GroupNo], ");
            builder.Append("\t[UserStr] ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[Group] ");
            builder.Append("WHERE ");
            builder.Append("\t[GroupId] = @GroupId ");
            builder.Append("\tAND [UserId] = @UserId ");
            SqlParameter[] commandParameters = new SqlParameter[2];
            commandParameters[0] = new SqlParameter("@GroupId", SqlDbType.Int);
            commandParameters[0].Value = groupId;
            commandParameters[1] = new SqlParameter("@UserId", SqlDbType.NVarChar, 20);
            commandParameters[1].Value = userId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString(), commandParameters);
            set.Tables[0].TableName = "Group";
            return set.Tables[0];
        }

        public DataTable SelectUserByGroupId(SqlConnection conn, string userId, int groupId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("SELECT ");
            builder.Append("\t[Users].[UserId], ");
            builder.Append("\t[Users].[UserName] ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[Users], ");
            builder.Append("\t[dbo].[Department], ");
            builder.Append("\t[dbo].[Group] ");
            builder.Append("WHERE ");
            builder.Append("\t[Group].[GroupId] = @GroupId ");
            builder.Append("\tAND [Group].[UserId] = @UserId ");
            builder.Append("\tAND CHARINDEX(',' + [Users].[UserId] + ',',',' + CAST([Group].[UserStr] AS nvarchar(4000)) + ',') > 0 ");
            builder.Append("\tAND [Users].[DeptId] = [Department].[DeptId] ");
            builder.Append("ORDER BY ");
            builder.Append("\t[Department].[DeptNo] ASC, ");
            builder.Append("\t[Users].[UserNo] ASC ");
            SqlParameter[] commandParameters = new SqlParameter[2];
            commandParameters[0] = new SqlParameter("@GroupId", SqlDbType.Int);
            commandParameters[0].Value = groupId;
            commandParameters[1] = new SqlParameter("@UserId", SqlDbType.NVarChar, 20);
            commandParameters[1].Value = userId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString(), commandParameters);
            set.Tables[0].TableName = "Group";
            return set.Tables[0];
        }

        public bool Set(SqlConnection conn, string userId, int groupId, string userStr)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("UPDATE [dbo].[Group] ");
            builder.Append("SET ");
            builder.Append("\t[UserStr] = @UserStr ");
            builder.Append("WHERE ");
            builder.Append("\t[GroupId] = @GroupId ");
            builder.Append("\tAND [UserId] = @UserId ");
            SqlParameter[] commandParameters = new SqlParameter[3];
            commandParameters[0] = new SqlParameter("@GroupId", SqlDbType.Int);
            commandParameters[0].Value = groupId;
            commandParameters[1] = new SqlParameter("@UserId", SqlDbType.NVarChar, 20);
            commandParameters[1].Value = userId;
            commandParameters[2] = new SqlParameter("@UserStr", SqlDbType.NText);
            commandParameters[2].Value = userStr;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, builder.ToString(), commandParameters);
            return true;
        }

        public bool Update(SqlConnection conn, GroupData data)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("UPDATE [dbo].[Group] ");
            builder.Append("SET ");
            builder.Append("\t[GroupName] = @GroupName, ");
            builder.Append("\t[GroupNo] = @GroupNo ");
            builder.Append("WHERE ");
            builder.Append("\t[GroupId] = @GroupId ");
            builder.Append("\tAND [UserId] = @UserId ");
            SqlParameter[] commandParameters = new SqlParameter[4];
            commandParameters[0] = new SqlParameter("@GroupId", SqlDbType.Int);
            commandParameters[0].Value = data.GroupId;
            commandParameters[1] = new SqlParameter("@UserId", SqlDbType.NVarChar, 20);
            commandParameters[1].Value = data.UserId;
            commandParameters[2] = new SqlParameter("@GroupName", SqlDbType.NVarChar, 50);
            commandParameters[2].Value = data.Name;
            commandParameters[3] = new SqlParameter("@GroupNo", SqlDbType.NVarChar, 20);
            commandParameters[3].Value = data.GroupNo;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, builder.ToString(), commandParameters);
            return true;
        }
    }
}

